﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dal;


namespace BdClient
{
    public class BdExecute
    {
        public const string ServerName = "(local)";
        public const string DatabaseName = "Univer";


        public EntityConnection EntityConnection()
         {
             try
             {
                 string providerName = "System.Data.SqlClient";
                 string serverName = ServerName;
                 string databaseName = DatabaseName;


                 var sqlBuilder = new SqlConnectionStringBuilder
                 {
                     DataSource = serverName,
                     InitialCatalog = databaseName,
                     IntegratedSecurity = true
                 };
                 string providerString = sqlBuilder.ToString();

                 var entityBuilder = new EntityConnectionStringBuilder
                 {
                     Provider = providerName,
                     ProviderConnectionString = providerString,
                     Metadata = @"res://*/Univer.csdl|
                            res://*/Univer.ssdl|
                            res://*/Univer.msl"
                 };
                 return new EntityConnection(entityBuilder.ToString());
                 
             }
             catch (Exception ex)
             {
               
             }
            return null;
         }

        public IEnumerable<getCars_Result> GetCars()
        {
            var r = new List<getCars_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.getCars());
            }
            return r;
        }

        public IEnumerable<GetTransaction_Result> GetTransacation()
        {
            var r = new List<GetTransaction_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetTransaction());
            }
            return r;
        }

        public IEnumerable<GetTransactionActiv_Result> GetTransacationActiv(DateTime time)
        {
            var r = new List<GetTransactionActiv_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetTransactionActiv(time));
            }
            return r;
        }

        public IEnumerable<GetTransactionEnd_Result> GetTransacationEnd(DateTime time)
        {
            var r = new List<GetTransactionEnd_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetTransactionEnd(time));
            }
            return r;
        }

        public IEnumerable<GetCarsPopular_Result> GetCarsPopular()
        {
            var r = new List<GetCarsPopular_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetCarsPopular());
            }
            return r;
        }

        public IEnumerable<GetClients_Result> GetClients()
        {
            var r = new List<GetClients_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetClients());
            }
            return r;
        }

        public IEnumerable<GetConstantClients_Result> GetConstantClients()
        {
            var r = new List<GetConstantClients_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetConstantClients());
            }
            return r;
        }

        public IEnumerable<GetMarks_Result> GetMarks()
        {
            var r =new List<GetMarks_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetMarks());
            }
            return r;
        }
        
        public IEnumerable<GetCarsBusy_Result> GetCarsBusy(DateTime time)
        {
            var r = new List<GetCarsBusy_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetCarsBusy(time));
            }
            return r;
        }

        public decimal AddCar(decimal idMark,decimal priceDay,decimal price)
        {
            var r = new List<AddCar_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.AddCar(idMark,price,priceDay));
            }
            return r.FirstOrDefault().id;
        }

        public decimal AddMark(string name)
        {
            var r = new List<AddMark_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.AddMark(name));
            }
            return r.FirstOrDefault().id;
        }

        public IEnumerable<GetPropertyCar_Result> GetPropertyCar()
        {
            var r = new List<GetPropertyCar_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetPropertyCar());
            }
            return r;
        }

        public void AddPropertyCar(decimal idCars,decimal idPropery)
        {
           
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

               context.AddPropertyCars(idCars,idPropery);
            }
        
        }

        public GetClient_Result GetClient(string secondName, decimal passport)
        {

            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

               return context.GetClient(secondName, passport).FirstOrDefault();
            }

        }

        public AddClient_Result AddClient(string name,string secondName,string patronic, decimal passport,decimal phone)
        {

            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                return context.AddClient(name,secondName,patronic, passport,phone).FirstOrDefault();
            }

        }

        public IEnumerable<GetCarsNotBusy_Result> GetCarsNotBusy (DateTime datestart,DateTime dateEnd, decimal price,string name)
        {
            var r = new List<GetCarsNotBusy_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.GetCarsNotBusy(datestart,dateEnd,price,name));
            }
            return r;
        }

        public decimal AddPreference(decimal idClient,decimal idMark,DateTime timeStart,DateTime timeEnd)
        {
            var r = new List<AddPreference_Result>();
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                r.AddRange(context.AddPreference(idClient,idMark,timeStart,timeEnd));
            }
            return r.FirstOrDefault().id;
        }

        public void AddPayment (decimal idCar,decimal idClient,decimal idPreference,DateTime timeStart,DateTime timeEnd,decimal summ)
        {
            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                context.AddPayment(idCar,idClient,idPreference, timeStart, timeEnd,summ);
            }
        }

        public void AddPreference_PropertyCars(decimal idPreference, decimal idPropery)
        {

            using (var context = new UniverEntities(EntityConnection()))
            {
                context.Connection.Open();

                context.AddPreference_PropertyCars(idPreference, idPropery);
            }

        }

    }
}
